package org.example.aitest.utils.gen;

import com.alibaba.fastjson2.util.DateUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.*;
import org.example.aitest.dto.InData;
import org.example.aitest.dto.TableColumn;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZonedDateTime;
import java.util.*;

import static org.example.aitest.utils.gen.ExcelUtil.isCompareXls;
import static org.example.aitest.utils.gen.ExcelUtil.isCompareXlsx;
import static org.hibernate.query.criteria.internal.ValueHandlerFactory.isNumeric;

/**
 * @date 2025年05月05日
 */
@Component
public class ExcelComp {
    @Autowired
    public DynamicTableGenerator dynamicTableGenerator;
    @Autowired
    private ApplicationContext applicationContext;

    /**
     * @description: TODO 创建新表，结构包含上传的excel文件，以及传入的serve接口方法调用关联的数据库所有数据-只要关联能查到
     * @date 2025/5/5
     * @version: 1.0
     * 参数1：excel文件
     * 2：所有数据库->数据库所有列属性集合
     * 3：查询关联表的数据类型
     * 4：对该表的查询service接口类型
    */
    public List<TableColumn> createTable(MultipartFile file, List<Map<String, Object>> ysbg_xlsx_0List, Class<?> clazz, Class<?> serve,Integer ty,Integer sl) throws Exception {
        String name = file.getOriginalFilename();
        // excel文件表，以及确定的表行每列数据
        List<TableMsg> compare = null;
        if(ty == null || ty == 0){
            compare = ExcelUtil.compare(file,sl,ty);
        } else if (ty == 1) {
            compare = ExcelUtil.getLong(file,sl,true);
        }

        Boolean isP = false;
        for (TableMsg tableMsg : compare) {
            String cellYName = tableMsg.getCellYName();
            String cellName = tableMsg.getCellName();
            for (Map<String, Object> stringMap : ysbg_xlsx_0List) {
                    String columnComment = stringMap.get("columnComment").toString();
                    if(columnComment.equals(cellYName)){ //表注释和excel列名完全对应
                        List<Object> list = tableMsg.getList();
                        for (Object object : list) {
                            Object instance = clazz.getDeclaredConstructor().newInstance();
                            Method method = clazz.getDeclaredMethod("getMapType");
                            Map<String,Class<?>> entityMap = (Map<String, Class<?>>) method.invoke(instance);
                            for (String s : entityMap.keySet()) {
                                if(s.equals(cellName)){
                                    Class<?> aClass = entityMap.get(s);
                                    Method declaredMethod = clazz.getDeclaredMethod("set" + toCamelCase(cellName,true), aClass);
                                    if(aClass.getCanonicalName().contains("BigDecimal")){
                                        declaredMethod.invoke(instance,new BigDecimal(String.valueOf(object)));
                                    }else if(aClass.getCanonicalName().contains("Date")){
                                        declaredMethod.invoke(instance, DateUtils.parseDate((String) object));
                                    }else {
                                        declaredMethod.invoke(instance, object.toString());
                                    }
                                }
                            }


                            Object bean = applicationContext.getBean(serve);
                            Method getData = serve.getMethod("getData", clazz);
                            Object invoke = getData.invoke(bean, instance);
                            if(!Objects.isNull(invoke)){ // 列名完全对应，并且数据对应，要求excel中有数据的列该列数值有唯一性
                                // 有匹配上的数据
                                isP = true;
                                break;
                            }
                        }
                    }
                }

        }

        //表字段处理
        List<TableColumn> list1 = new ArrayList<>();
        if(isP){ // 有匹配上内容则证明该数据对应，因为匹配上都是有唯一性的数据
            //接下来做数据填充--做数据库还是直接生成excel表？--选择做数据库，直接生成excel表需要知道具体填充位置，毕竟麻烦
            //先将匹配上的原表内容加入
            for (TableMsg tableMsg : compare) {
                // 将关联表的所有表字段设置进新表
                for (Map<String, Object> objectMap : ysbg_xlsx_0List) {
                    TableColumn tableColumn = new TableColumn();
                    String columnName = objectMap.get("columnName").toString();
                    if (columnName.equals("id") || columnName.equals("create_time") || columnName.equals("update_time")) { //排除id,创建时间和更新时间
                        continue;
                    }
                    String columnType = objectMap.get("columnType").toString();
                    int[] ints = DbTypeParser.parseTypeInfo(columnType);
                    String dataType = objectMap.get("dataType").toString();
                    String columnComment = objectMap.get("columnComment").toString();

                    tableColumn.setName(columnName);
                    tableColumn.setType(dataType);
                    tableColumn.setLength(ints[0]);
                    tableColumn.setComment(columnComment);
                    list1.add(tableColumn);
                }

                // 文件后缀
                String la = name.substring(name.lastIndexOf(".") + 1);
                if (la.equals("xlsx")) {
                    // 将excel中有的列，而数据库没有的添加进新表
                    XSSFWorkbook excel = new XSSFWorkbook(file.getInputStream());
                    XSSFSheet sheet = excel.getSheetAt(tableMsg.getBNo());
                    XSSFRow row = sheet.getRow(tableMsg.getHNo());
                    int lastCellNum = row.getLastCellNum();
                    //命名集合，为了对重名做处理才添加
                    List<String> list = new ArrayList<>();
                    //备注集合
                    List<String> remarkList = new ArrayList<>();
                    for (int i = 0; i < lastCellNum; i++) {
                        Boolean isCF = false;
                        //排除掉已经设置的列属性
                        XSSFCell cell = row.getCell(i);
                        DataFormatter formatter = new DataFormatter();
                        String stringCellValue = formatter.formatCellValue(cell);
                        for (Map<String, Object> map : ysbg_xlsx_0List) {
                            String columnComment = map.get("columnComment").toString();
                            if (stringCellValue.equals(columnComment)) {
                                isCF = true;
                                break;
                            }
                        }
                        if (isCF) {
                            continue;
                        }
                        if(StringUtils.isEmpty(stringCellValue)){
                            continue;
                        }
                        //取首拼音
                        String firstLetters = PinyinUtils.getFirstLetters(stringCellValue);
                        //对命名做规范处理
                        String name1 = PinyinUtils.sanitizeTableName(firstLetters);
                        list.add(name1);
                        remarkList.add(stringCellValue);
                    }

                    //对命名集合做重名处理
                    for (int i = 0; i < list.size(); i++) {
                        for (int j = i + 1; j < list.size(); j++) {
                            if (list.get(i).equals(list.get(j))) {
                                //依次与后面所有数据比较，若相同则重名，重名加后缀
                                String s = list.get(i);
                                list.remove(j);
                                list.add(j, s + "_" + j);
                            }
                        }
                    }

                    //未设置的数据都统一设置为字符串类型
                    for (int i = 0; i < list.size(); i++) {
                        String s = list.get(i);
                        TableColumn tableColumn = new TableColumn();
                        tableColumn.setName(s);
                        tableColumn.setType("VARCHAR");
                        tableColumn.setLength(100);
                        tableColumn.setComment(remarkList.get(i));
                        list1.add(tableColumn);
                    }
                } else if (la.equals("xls")) {
                    // 将excel中有的列，而数据库没有的添加进新表
                    HSSFWorkbook excel = new HSSFWorkbook(file.getInputStream());
                    HSSFSheet sheet = excel.getSheetAt(tableMsg.getBNo());
                    HSSFRow row = sheet.getRow(tableMsg.getHNo());
                    int lastCellNum = row.getLastCellNum();
                    //命名集合，为了对重名做处理才添加
                    List<String> list = new ArrayList<>();
                    //备注集合
                    List<String> remarkList = new ArrayList<>();
                    for (int i = 0; i < lastCellNum; i++) {
                        Boolean isCF = false;
                        //排除掉已经设置的列属性
                        HSSFCell cell = row.getCell(i);
                        DataFormatter formatter = new DataFormatter();
                        String stringCellValue = formatter.formatCellValue(cell);
                        for (Map<String, Object> map : ysbg_xlsx_0List) {
                            String columnComment = map.get("columnComment").toString();
                            if (stringCellValue.equals(columnComment)) {
                                isCF = true;
                                break;
                            }
                        }
                        if (isCF) {
                            continue;
                        }
                        //取首拼音
                        String firstLetters = PinyinUtils.getFirstLetters(stringCellValue);
                        //对命名做规范处理
                        String name1 = PinyinUtils.sanitizeTableName(firstLetters);
                        list.add(name1);
                        remarkList.add(stringCellValue);
                    }

                    //对命名集合做重名处理
                    for (int i = 0; i < list.size(); i++) {
                        for (int j = i + 1; j < list.size(); j++) {
                            if (list.get(i).equals(list.get(j))) {
                                //依次与后面所有数据比较，若相同则重名，重名加后缀
                                String s = list.get(i);
                                list.remove(j);
                                list.add(j, s + "_" + j);
                            }
                        }
                    }

                    //未设置的数据都统一设置为字符串类型
                    for (int i = 0; i < list.size(); i++) {
                        String s = list.get(i);
                        TableColumn tableColumn = new TableColumn();
                        tableColumn.setName(s);
                        tableColumn.setType("VARCHAR");
                        tableColumn.setLength(100);
                        tableColumn.setComment(remarkList.get(i));
                        list1.add(tableColumn);
                    }
                }
            }
        }

        return list1;
    }

    /**
     * TODO 往新表中添加匹配数据-包括excel表已有数据和数据库匹配的表数据
     * 参数1：excel文件
     * 参数2：关联数据库的数据类型
     * 参数3：关联数据库的service接口类型
     * */
    public List<List<InData>> insertData(MultipartFile file,Class<?> clazz,Class<?> serve,Integer ty,Integer sl) throws Exception {
        String name = file.getOriginalFilename();
        String la = name.substring(name.lastIndexOf(".") + 1);
        if(la.equals("xlsx")) {
            XSSFWorkbook excel = new XSSFWorkbook(file.getInputStream());
            //往新表中添加数据
            //添加第二步上传比较文件自身的数据
            List<TableMsg> compare = null;
            if(ty == null || ty == 0){
                compare = ExcelUtil.compare(file,sl,ty);
            } else if (ty == 1) {
                compare = ExcelUtil.getLong(file,sl,true);
            }

            //随意取一个数据，只是需要获取的数据是哪张表，哪一行
            TableMsg tableMsg = compare.get(0);
            Integer bNo = tableMsg.getBNo();
            Integer hNo = tableMsg.getHNo();

            XSSFSheet sheet = excel.getSheetAt(bNo);
            //设置列名集合
            List<String> lNames = new ArrayList<>();
            XSSFRow row1 = sheet.getRow(hNo);
            int lastCellNum1 = row1.getLastCellNum();
            for (int i = 0; i < compare.size(); i++) {
                TableMsg tableMsg1 = compare.get(i);
                lNames.add(tableMsg1.getCellName());
            }

            List<List<InData>> list = new ArrayList<>();
            Boolean isNum = false;
            //所有行
            int lastRowNum = sheet.getLastRowNum() + 1;
            for (int i = hNo + 1; i < lastRowNum; i++) {
                //一行数据
                List<InData> inDatas = new ArrayList<>();
                XSSFRow row = sheet.getRow(i);
                int t = 0;
                for (int j = 0; j < lastCellNum1; j++) {
                    XSSFCell cell = row.getCell(j);
                    if (cell != null) {
                        DataFormatter formatter = new DataFormatter();
                        XSSFCell cell1 = row1.getCell(j);
                        String s = formatter.formatCellValue(cell1);
                        InData inData = new InData();

                        String stringCellValue = formatter.formatCellValue(cell);
                        //添加excel表中数据
                        inData.setName(lNames.get(t));
                        inData.setValue(stringCellValue);
                        inData.setComment(s);
                        if(ty != null && ty == 1){
                            if(!isCompareXlsx(cell1)){
                                continue;
                            }
                        }
                        inDatas.add(inData);

                        // 记录添加数据库数据前数据量
                        int perNum = inDatas.size();
                        //添加数据库中数据
                        if (StringUtils.isEmpty(stringCellValue)) {
                            continue;
                        }
                        extractedXlsx(lNames, inDatas, t, cell, clazz, serve);
                        //添加数据库数据后数据量
                        int lastNum = inDatas.size();
                        if (perNum < lastNum) {
                            // 证明数据库有匹配的数据
                            isNum = true;
                        }
                        t++;
                    }
                }
                list.add(inDatas);
            }
            if (isNum) {
                return list;
            } else {
                list.clear();
                return list;
            }
        } else if (la.equals("xls")) {
            HSSFWorkbook excel = new HSSFWorkbook(file.getInputStream());
            //往新表中添加数据
            //添加第二步上传比较文件自身的数据
            List<TableMsg> compare = null;
            if(ty == null || ty == 0){
                compare = ExcelUtil.compare(file,sl,ty);
            } else if (ty == 1) {
                compare = ExcelUtil.getLong(file,sl,true);
            }

            //随意取一个数据，只是需要获取的数据是哪张表，哪一行
            TableMsg tableMsg = compare.get(0);
            Integer bNo = tableMsg.getBNo();
            Integer hNo = tableMsg.getHNo();

            HSSFSheet sheet = excel.getSheetAt(bNo);
            //设置列名集合
            List<String> lNames = new ArrayList<>();
            HSSFRow row1 = sheet.getRow(hNo);
            int lastCellNum1 = row1.getLastCellNum();
            for (int i = 0; i < compare.size(); i++) {
                TableMsg tableMsg1 = compare.get(i);
                lNames.add(tableMsg1.getCellName());
            }

            List<List<InData>> list = new ArrayList<>();
            Boolean isNum = false;
            //所有行
            int lastRowNum = sheet.getLastRowNum() + 1;
            for (int i = hNo + 1; i < lastRowNum; i++) {
                //一行数据
                List<InData> inDatas = new ArrayList<>();
                HSSFRow row = sheet.getRow(i);
                int t = 0;
                for (int j = 0; j < lastCellNum1; j++) {
                    HSSFCell cell = row.getCell(j);
                    if (cell != null) {
                        DataFormatter formatter = new DataFormatter();
                        HSSFCell cell1 = row1.getCell(j);
                        String s = formatter.formatCellValue(cell1);
                        InData inData = new InData();

                        String stringCellValue = formatter.formatCellValue(cell);
                        //添加excel表中数据
                        inData.setName(lNames.get(t));
                        inData.setValue(stringCellValue);
                        inData.setComment(s);
                        if(ty != null && ty == 1){
                            if(!isCompareXls(cell1)){
                                continue;
                            }
                        }
                        inDatas.add(inData);

                        // 记录添加数据库数据前数据量
                        int perNum = inDatas.size();
                        //添加数据库中数据
                        if (StringUtils.isEmpty(stringCellValue)) {
                            continue;
                        }
                        extractedXls(lNames, inDatas, t, cell, clazz, serve);
                        //添加数据库数据后数据量
                        int lastNum = inDatas.size();
                        if (perNum < lastNum) {
                            // 证明数据库有匹配的数据
                            isNum = true;
                        }
                        t++;
                    }
                }
                list.add(inDatas);
            }
            if (isNum) {
                return list;
            } else {
                list.clear();
                return list;
            }
        }
        return null;
    }

    private void extractedXlsx(List<String> lNames, List<InData> inDatas, int j,XSSFCell cell,Class<?> clazz,Class<?> serve) throws Exception {
        //反射调用实体类的方法，获取各个参数和其对应类型
        Object instance = clazz.getDeclaredConstructor().newInstance();
        Method method = clazz.getDeclaredMethod("getMapType");
        Map<String,Class<?>> entityMap = (Map<String, Class<?>>) method.invoke(instance);
        for (String s : entityMap.keySet()) {
            if(s.equals(lNames.get(j))){
                String camelCase = toCamelCase(lNames.get(j), true);
                Class<?> aClass = entityMap.get(s);
                Method declaredMethod = clazz.getDeclaredMethod("set" + camelCase, aClass);
                switch (cell.getCellType()) {
                    case STRING:
                        declaredMethod.invoke(instance,cell.getStringCellValue());
                        break;
                    case NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            //时间
                            DataFormatter formatter = new DataFormatter();
                            declaredMethod.invoke(instance,formatter.formatCellValue(cell));
                        } else {
                            // 数字
                            DataFormatter formatter = new DataFormatter();
                            declaredMethod.invoke(instance,new BigDecimal(formatter.formatCellValue(cell)));
                        }
                        break;
                    case BOOLEAN:
                        declaredMethod.invoke(instance,cell.getBooleanCellValue());
                        break;
                    case FORMULA:
                        declaredMethod.invoke(instance,cell.getCellFormula());
                        break;
                    default:
                        break;
                }

                Object bean = applicationContext.getBean(serve);
                Method getData = serve.getMethod("getData", clazz);
                Object invoke = getData.invoke(bean, instance);
                if (!Objects.isNull(invoke)) {
                    //移除已经添加的字段值
                    Method getMap = clazz.getDeclaredMethod("getInData");
                    List<InData> inDataList = (List<InData>) getMap.invoke(invoke);
                    if (!inDataList.isEmpty()) {
                        for (InData inData : inDataList) {
                            //移除已经添加过的值
                            if (inData.getName().equals(lNames.get(j))) {
                                continue;
                            }
                            Object object = inData.getValue();
                            InData inData1 = new InData();
                            inData1.setName(inData.getName());
                            if(isDateTimeType(object)){
                                inData1.setValue(org.example.aitest.utils.DateUtils.parseDate2String((Date) inData.getValue(),"yyyy-MM-dd HH:MM:SS"));
                            }else {
                                inData1.setValue(inData.getValue());
                            }
                            inData1.setComment(inData.getComment());
                            inDatas.add(inData1);
                        }
                    }
                }
            }
        }
    }

    private void extractedXls(List<String> lNames, List<InData> inDatas, int j,HSSFCell cell,Class<?> clazz,Class<?> serve) throws Exception {
        //反射调用实体类的方法，获取各个参数和其对应类型
        Object instance = clazz.getDeclaredConstructor().newInstance();
        Method method = clazz.getDeclaredMethod("getMapType");
        Map<String,Class<?>> entityMap = (Map<String, Class<?>>) method.invoke(instance);
        for (String s : entityMap.keySet()) {
            if(s.equals(lNames.get(j))){
                String camelCase = toCamelCase(lNames.get(j), true);
                Class<?> aClass = entityMap.get(s);
                Method declaredMethod = clazz.getDeclaredMethod("set" + camelCase, aClass);
                switch (cell.getCellType()) {
                    case STRING:
                        declaredMethod.invoke(instance,cell.getStringCellValue());
                        break;
                    case NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            //时间
                            DataFormatter formatter = new DataFormatter();
                            declaredMethod.invoke(instance,formatter.formatCellValue(cell));
                        } else {
                            // 数字
                            DataFormatter formatter = new DataFormatter();
                            declaredMethod.invoke(instance,new BigDecimal(formatter.formatCellValue(cell)));
                        }
                        break;
                    case BOOLEAN:
                        declaredMethod.invoke(instance,cell.getBooleanCellValue());
                        break;
                    case FORMULA:
                        declaredMethod.invoke(instance,cell.getCellFormula());
                        break;
                    default:
                        break;
                }

                Object bean = applicationContext.getBean(serve);
                Method getData = serve.getMethod("getData", clazz);
                Object invoke = getData.invoke(bean, instance);
                if (!Objects.isNull(invoke)) {
                    //移除已经添加的字段值
                    Method getMap = clazz.getDeclaredMethod("getInData");
                    List<InData> inDataList = (List<InData>) getMap.invoke(invoke);
                    if (!inDataList.isEmpty()) {
                        for (InData inData : inDataList) {
                            //移除已经添加过的值
                            if (inData.getName().equals(lNames.get(j))) {
                                continue;
                            }
                            Object object = inData.getValue();
                            InData inData1 = new InData();
                            inData1.setName(inData.getName());
                            if(isDateTimeType(object)){
                                inData1.setValue(org.example.aitest.utils.DateUtils.parseDate2String((Date) inData.getValue(),"yyyy-MM-dd HH:MM:SS"));
                            }else {
                                inData1.setValue(inData.getValue());
                            }
                            inData1.setComment(inData.getComment());
                            inDatas.add(inData1);
                        }
                    }
                }
            }
        }
    }

    public List<List<InData>> exNoExists(MultipartFile file,List<List<InData>> lists) throws Exception {
        String name = file.getOriginalFilename();
        String la = name.substring(name.lastIndexOf(".") + 1);
        if(la.equals("xlsx")) {
            XSSFWorkbook excel = new XSSFWorkbook(file.getInputStream());
            //有几张表
            int sheets = excel.getNumberOfSheets();
            //比较表目前只做一个表的比较
            if(sheets>1){
                throw new RuntimeException("excel比较的这个表格只能有一个");
            }
            XSSFSheet sheet = excel.getSheetAt(0);
            //有几行
            int lastRowNum = sheet.getLastRowNum();
            //最终取的行
            int hMsg = 0;
            int temp = 0;
            //取出第一次出现的不为空的最长一行
            for (int j = 0; j < lastRowNum; j++) {
                XSSFRow row = sheet.getRow(j);
                //有几列
                int lastCellNum = row.getLastCellNum();
                //该行有多少列不为空
                int hNum = 0;
                for (int k = 0; k < lastCellNum; k++) {
                    XSSFCell cell = row.getCell(k);
                    if(cell != null){
                        hNum++;
                    }
                }
                if(j == 0) {
                    //第一次赋值
                    temp = hNum;
                }else{
                    if(temp < hNum){
                        temp = hNum;
                        hMsg = j;
                    }
                }
            }
            //确定行必为字符串
            XSSFRow row = sheet.getRow(hMsg);
            int lastCellNum = row.getLastCellNum();
            //列名集合
            List<String> listName = new ArrayList<>();
            for (int j = 0; j < lastCellNum; j++) {
                XSSFCell cell = row.getCell(j);
                if(cell == null){
                    continue;
                }
                String stringCellValue = cell.getStringCellValue();
                if(StringUtils.isEmpty(stringCellValue)){
                    continue;
                }
                String firstLetters = PinyinUtils.getFirstLetters(stringCellValue);
                String s = PinyinUtils.sanitizeTableName(firstLetters);
                listName.add(s);
            }
            //数据集合
            //所有行数据
            List<Map<String,Object>> listList = new ArrayList<>();
            List<List<Object>> allHData = new ArrayList<>();
            for (int j = hMsg+1; j <= lastRowNum; j++) {
                XSSFRow row1 = sheet.getRow(j);
                int lastCellNum1 = row1.getLastCellNum();
                //一行数据
                List<Object> listData = new ArrayList<>();
                String stringCellValue = null;
                for (int k = 0; k < lastCellNum1; k++) {
                    XSSFCell cell = row1.getCell(k);
                    if (cell != null) {
                        switch (cell.getCellType()) {
                            case STRING:
                                stringCellValue = cell.getStringCellValue();
                                break;
                            case NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    stringCellValue = org.example.aitest.utils.DateUtils.parseDate2String(cell.getDateCellValue());
                                } else {
                                    // 数字以原格式输出，避免添加0
                                    DataFormatter formatter = new DataFormatter();
                                    stringCellValue = formatter.formatCellValue(cell);
                                    // 原格式输出后若千位加逗号，去除逗号
                                    stringCellValue = stringCellValue.replace(",", "");
                                }
                                break;
                            case BOOLEAN:
                                stringCellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case FORMULA:
                                //获取公式计算后的值
                                XSSFFormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
                                CellValue evaluate = evaluator.evaluate(cell);
                                switch (evaluate.getCellType()) {
                                    case NUMERIC:
                                        stringCellValue = String.valueOf(evaluate.getNumberValue());
                                        break;
                                    case STRING:
                                        stringCellValue = evaluate.getStringValue();
                                        break;
                                    case BOOLEAN:
                                        stringCellValue = String.valueOf(evaluate.getBooleanValue());
                                        break;
                                    case ERROR:
                                        System.out.println("值: 错误");
                                        stringCellValue = "";
                                        break;
                                    default:
                                        System.out.println("值: 未知类型");
                                        stringCellValue = "";
                                }
                                break;
                            default:
                                continue;
                        }
                    } else {
                        //没有数据跳过
                        continue;
                    }
                    listData.add(stringCellValue);
                }
                allHData.add(listData);
            }
            for (int j = 0; j < allHData.size(); j++) {
                Map<String,Object> map = new HashMap<>();
                List<Object> list = allHData.get(j);
                for (int k = 0; k < listName.size(); k++) {
                    map.put(listName.get(k),list.get(k));
                }
                listList.add(map);
            }
            List<List<InData>> lists1 = matchAndFilter(listList, lists);
            return lists1;
//            }
        } else if (la.equals("xls")) {
            HSSFWorkbook excel = new HSSFWorkbook(file.getInputStream());
            //有几张表
            int sheets = excel.getNumberOfSheets();
            //比较表目前只做一个表的比较
            if(sheets>1){
                throw new RuntimeException("excel比较的这个表格只能有一个");
            }
            HSSFSheet sheet = excel.getSheetAt(0);
            //有几行
            int lastRowNum = sheet.getLastRowNum();
            //最终取的行
            int hMsg = 0;
            int temp = 0;
            //取出第一次出现的不为空的最长一行
            for (int j = 0; j < lastRowNum; j++) {
                HSSFRow row = sheet.getRow(j);
                //有几列
                int lastCellNum = row.getLastCellNum();
                //该行有多少列不为空
                int hNum = 0;
                for (int k = 0; k < lastCellNum; k++) {
                    HSSFCell cell = row.getCell(k);
                    if(cell != null){
                        hNum++;
                    }
                }
                if(j == 0) {
                    //第一次赋值
                    temp = hNum;
                }else{
                    if(temp < hNum){
                        temp = hNum;
                        hMsg = j;
                    }
                }
            }
            //确定行必为字符串
            HSSFRow row = sheet.getRow(hMsg);
            int lastCellNum = row.getLastCellNum();
            //列名集合
            List<String> listName = new ArrayList<>();
            for (int j = 0; j < lastCellNum; j++) {
                HSSFCell cell = row.getCell(j);
                String stringCellValue = cell.getStringCellValue();
                String firstLetters = PinyinUtils.getFirstLetters(stringCellValue);
                String s = PinyinUtils.sanitizeTableName(firstLetters);
                listName.add(s);
            }
            //数据集合
            //所有行数据
            List<Map<String,Object>> listList = new ArrayList<>();
            List<List<Object>> allHData = new ArrayList<>();
            for (int j = hMsg+1; j <= lastRowNum; j++) {
                HSSFRow row1 = sheet.getRow(j);
                int lastCellNum1 = row1.getLastCellNum();
                //一行数据
                List<Object> listData = new ArrayList<>();
                String stringCellValue = null;
                for (int k = 0; k < lastCellNum1; k++) {
                    HSSFCell cell = row1.getCell(k);
                    if (cell != null) {
                        switch (cell.getCellType()) {
                            case STRING:
                                stringCellValue = cell.getStringCellValue();
                                break;
                            case NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    stringCellValue = org.example.aitest.utils.DateUtils.parseDate2String(cell.getDateCellValue());
                                } else {
                                    // 数字以原格式输出，避免添加0
                                    DataFormatter formatter = new DataFormatter();
                                    stringCellValue = formatter.formatCellValue(cell);
                                    // 原格式输出后若千位加逗号，去除逗号
                                    stringCellValue = stringCellValue.replace(",", "");
                                }
                                break;
                            case BOOLEAN:
                                stringCellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case FORMULA:
                                //获取公式计算后的值
                                HSSFFormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
                                CellValue evaluate = evaluator.evaluate(cell);
                                switch (evaluate.getCellType()) {
                                    case NUMERIC:
                                        stringCellValue = String.valueOf(evaluate.getNumberValue());
                                        break;
                                    case STRING:
                                        stringCellValue = evaluate.getStringValue();
                                        break;
                                    case BOOLEAN:
                                        stringCellValue = String.valueOf(evaluate.getBooleanValue());
                                        break;
                                    case ERROR:
                                        System.out.println("值: 错误");
                                        stringCellValue = "";
                                        break;
                                    default:
                                        System.out.println("值: 未知类型");
                                        stringCellValue = "";
                                }
                                break;
                            default:
                                continue;
                        }
                    } else {
                        //没有数据跳过
                        continue;
                    }
                    listData.add(stringCellValue);
                }
                allHData.add(listData);
            }
            for (int j = 0; j < allHData.size(); j++) {
                Map<String,Object> map = new HashMap<>();
                List<Object> list = allHData.get(j);
                for (int k = 0; k < listName.size(); k++) {
                    map.put(listName.get(k),list.get(k));
                }
                listList.add(map);
            }
            List<List<InData>> lists1 = matchAndFilter(listList, lists);
            return lists1;
        }
        return null;
    }

    public List<List<InData>> matchAndFilter(List<Map<String, Object>> listList, List<List<InData>> lists) {
        Set<List<InData>> matchedLists = new HashSet<>();

        // 第一阶段：直接匹配
        for (Map<String, Object> map : listList) {
            for (Map.Entry<String, Object> entry : map.entrySet()) {
                String key = entry.getKey();
                Object value = entry.getValue();

                for (List<InData> inDataList : lists) {
                    boolean isMatched = inDataList.stream()
                            .anyMatch(inData -> key.equals(inData.getName()) &&
                                    valuesMatch(value, inData.getValue()));

                    if (isMatched) {
                        matchedLists.add(inDataList);
                    }
                }
            }
        }

        // 第二阶段：通过isOnly=true的项扩展匹配
        Set<List<InData>> extendedMatches = new HashSet<>(matchedLists);
        Set<InData> onlyItems = new HashSet<>();

        matchedLists.forEach(list ->
                list.stream()
                        .filter(InData::getIsOnly)
                        .forEach(onlyItems::add)
        );

        for (List<InData> inDataList : lists) {
            if (!matchedLists.contains(inDataList)) {
                boolean isExtendedMatch = inDataList.stream()
                        .anyMatch(item -> onlyItems.stream()
                                .anyMatch(onlyItem -> isFullMatch(onlyItem, item)));

                if (isExtendedMatch) {
                    extendedMatches.add(inDataList);
                }
            }
        }

        // 合并结果并去重
        List<InData> mergedResult = new ArrayList<>();
        Set<String> seenComments = new HashSet<>();

        extendedMatches.forEach(list ->
                list.forEach(item -> {
                    if (!seenComments.contains(item.getComment())) {
                        mergedResult.add(item);
                        seenComments.add(item.getComment());
                    }
                })
        );

        List<List<InData>> finalResult = new ArrayList<>();
        finalResult.add(mergedResult);

        return finalResult;
    }

    /**
     * 值匹配逻辑（处理String类型的数字值）
     */
    private boolean valuesMatch(Object mapValue, Object inDataValue) {
        // 统一转为String处理
        String strMapValue = mapValue.toString();
        String strInDataValue = inDataValue.toString();

        try {
            // 尝试解析为数字（保留2位小数）
            BigDecimal decMap = new BigDecimal(strMapValue).setScale(2, BigDecimal.ROUND_HALF_UP);
            BigDecimal decInData = new BigDecimal(strInDataValue).setScale(2, BigDecimal.ROUND_HALF_UP);
            return decMap.compareTo(decInData) == 0;
        } catch (NumberFormatException e) {
            // 不是数字则直接比较字符串
            return strMapValue.equals(strInDataValue);
        }
    }

    /**
     * 完整匹配检查
     */
    private boolean isFullMatch(InData a, InData b) {
        return Objects.equals(a.getName(), b.getName()) &&
                valuesMatch(a.getValue(), b.getValue()) &&
                Objects.equals(a.getComment(), b.getComment()) &&
                Objects.equals(a.getIsOnly(), b.getIsOnly());
    }

    public void addTableToExcel(List<Map<String, Object>> srhtXlsxBak_0ServiceCm, XSSFWorkbook excel, List srhtXlsxBak_0ServiceAll) throws Exception {
        int lsize = srhtXlsxBak_0ServiceCm.size();

        // 创建新文件
        XSSFSheet sheet = excel.createSheet("匹配后数据表");

        // 第一行为注释内容
        XSSFRow row1 = sheet.getRow(0);
        //防止排除不需要字段后出现空列
        Boolean isJ = false;
        int j = 1;

        for (int p = 0; p < lsize; p++) {
            if(isJ){
                j--;
            }
            Map<String, Object> objectMap = srhtXlsxBak_0ServiceCm.get(p);
            String columnName = objectMap.get("columnName").toString();
            if(columnName.equalsIgnoreCase("id")
                    || columnName.equalsIgnoreCase("create_time")
                    || columnName.equalsIgnoreCase("update_time")){ //排除不需要的字段
                isJ = true;
                continue;
            }

            if(row1 == null){
                row1 = sheet.createRow(j);
            }
            XSSFCell cell1 = row1.getCell(j);
            if(cell1 == null){
                cell1 = row1.createCell(j);
            }
            cell1.setCellValue(objectMap.get("columnComment").toString());

            isJ = false;
            j++;
        }

        //第二行开始添加数据库内容
        for (int p = 1; p < srhtXlsxBak_0ServiceAll.size()+1; p++) {
            XSSFRow row2 = sheet.getRow(p);
            if(row2 == null){
                row2 = sheet.createRow(p);
            }
            Object o = srhtXlsxBak_0ServiceAll.get(p - 1);
            if(Objects.nonNull(o)) {
                Class<?> aClass = o.getClass();
                Method getAllGet = aClass.getMethod("getAllGet");
                List<Object> allGet = (List<Object>) getAllGet.invoke(o);
                for (int k = 0; k < allGet.size(); k++) {
                    Object allDataType = allGet.get(k);
                    if(Objects.nonNull(allDataType)) {
                        XSSFCell cell1 = row2.getCell(k);
                        if (cell1 == null) {
                            cell1 = row2.createCell(k);
                        }
                        if (allDataType instanceof String) {
                            cell1.setCellValue((String) allDataType);
                        } else if (allDataType instanceof BigDecimal) {
                            cell1.setCellValue(allDataType.toString());
                        } else if (isDateTimeType(allDataType)) {
                            cell1.setCellValue(org.example.aitest.utils.DateUtils.parseDate2String((Date) allDataType));
                        }
                    }
                }
            }
        }
    }

    //返回具有唯一字段的表对应表和字段
    public List<TableColumn> getTableOnlyMap(List<Map<String,Object>> list,String tableName,List<TableColumn> tableColumns){
        List<TableColumn> listT = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> map = list.get(i);
            String columnKey = (String) map.get("columnKey");
            if(columnKey.equals("UNI")){
                TableColumn tableColumn = new TableColumn();
                String columnName = (String) map.get("columnName");
                String dataType = (String) map.get("dataType");
                String columnComment = (String) map.get("columnComment");
                if(dataType.equalsIgnoreCase("varchar")){
                    tableColumn.setLength(100);
                } else if (dataType.equalsIgnoreCase("decimal")) {
                    tableColumn.setLength(20);
                }
                tableColumn.setName(columnName);
                tableColumn.setType(dataType);
                tableColumn.setComment(columnComment);
                tableColumn.setOnly(true);
                listT.add(tableColumn);
            }
        }
        if(tableColumns.size() > 0){
            //该表有匹配的数据才返回该唯一字段
            return listT;
        }else {
            return null;
        }
    }

    // 判断数据是否是时间类型
    public boolean isDateTimeType(Object obj) {
        return obj instanceof Date ||
                obj instanceof java.sql.Date ||
                obj instanceof Timestamp ||
                obj instanceof LocalDate ||
                obj instanceof LocalDateTime ||
                obj instanceof ZonedDateTime;
    }

    private String toCamelCase(String str, boolean isBigCamel) {
        String[] parts = str.split("_");
        StringBuilder result = new StringBuilder();

        for (int i = 0; i < parts.length; i++) {
            String part = parts[i];

            // 跳过空部分（连续下划线情况）
            if (part.isEmpty()) {
                continue;
            }

            // 处理数字情况：如果下一部分是数字，则保持原样
            if (i < parts.length - 1 && isNumeric(parts[i + 1])) {
                if (i == 0 && !isBigCamel) {
                    result.append(part.toLowerCase());
                } else {
                    result.append(part.substring(0, 1).toUpperCase())
                            .append(part.substring(1).toLowerCase());
                }
                result.append("_").append(parts[i + 1]); // 添加下划线和数字部分
                i++; // 跳过已处理的数字部分
                continue;
            }

            // 常规处理
            if (i == 0 && !isBigCamel) {
                result.append(part.toLowerCase());
            } else {
                result.append(part.substring(0, 1).toUpperCase())
                        .append(part.substring(1).toLowerCase());
            }
        }

        return result.toString();
    }
}
